package database

import (
	"database/sql"
	"errors"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"go-sec-code/model"
	"go-sec-code/utils"
	"gopkg.in/yaml.v2"
	"io/ioutil"
	"log"
	"os"
)

type Config struct {
	Database DatabaseConfig `yaml:"database"`
}

type DatabaseConfig struct {
	User     string `yaml:"user"`
	Password string `yaml:"password"`
	Name     string `yaml:"name"`
}

func loadConfig(filename string) (*Config, error) {
	// 读取YAML文件
	yamlFile, err := os.Open(filename)
	if err != nil {
		return nil, fmt.Errorf("无法打开文件: %v", err)
	}
	defer yamlFile.Close()

	// 读取文件内容
	byteValue, err := ioutil.ReadAll(yamlFile)
	if err != nil {
		return nil, fmt.Errorf("无法读取文件: %v", err)
	}

	// 解析YAML内容
	var config Config
	err = yaml.Unmarshal(byteValue, &config)
	if err != nil {
		return nil, fmt.Errorf("无法解析YAML: %v", err)
	}

	return &config, nil
}

type MysqlUser struct {
}

func NewMysqlUser() *MysqlUser {
	return &MysqlUser{}
}


func (u *MysqlUser) ConnectDB() (*sql.DB, error) {
	return u.ConnectDBCustom("root", "111111", "localhost", "3306")
}

func (*MysqlUser) ConnectDBCustom(username string, password string, host string, port string) (*sql.DB, error) {
	// 白名单限制可连接的IP
	if !isWhiteHost(host) {
		return nil, errors.New("Invaild host")
	}
	// 白名单限制可连接的端口
	if !isWhitePort(port) {
		return nil, errors.New("Invaild port")
	}

	config, _ := loadConfig("config/config.yml")
	// 通过配置文件，硬编码连接串参数
	db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s:%s)/go_sec_code",
		config.Database.User, config.Database.Password, host, port))
	if err != nil {
		return nil, err
	}
	// 测试连接
	if err := db.Ping(); err != nil {
		return nil, err
	}
	return db, nil
}

func isWhiteHost(host string) bool {
	whiteHostList := []string{"localhost", "172.30.14.55"}
	for _, whiteHost := range whiteHostList {
		if host == whiteHost{
			return true
		}
	}
	return false
}

func isWhitePort(port string) bool {
	whitePortList := []string{"3306", "6379"}
	for _, whitePort := range whitePortList {
		if port == whitePort {
			return true
		}
	}
	return false
}

func (*MysqlUser) SelectByName(db *sql.DB, name string, orderField string) []model.User {
	sql := "select id, name, age, sex, password from user where name like '%" + name + "%'"		//存在sql注入
	//sql := db.Query("select id, name, age, sex, password from user where name = '" + name + "'");
	//sql := db.Query("select id, name, age, sex, password from user where name = ? order by " + orderField + " desc", name);	//存在sql注入
	//sql := db.Query("select id, name, age, sex, password from user where name = ? order by ? desc", name, orderField);	//由于预编译导致order by 失效

	//sql := db.Query("select id, name, age, sex, password from user where name like concat('%', ?, '%')", name);
	//sql := db.Query("select id, name, age, sex, password from user where name like ?", "%" + name + "%");
	log.Printf("sql: %v\n", sql)
	rows, err := db.Query(sql);

	if err != nil {
		log.Println(err)
		return nil
	}
	defer rows.Close()

	var users []model.User

	for rows.Next() {
		var user model.User
		if err := rows.Scan(&user.Id, &user.Name, &user.Age, &user.Sex, &user.Password); err != nil {
			log.Fatal(err)
		}
		users = append(users, user)
	}

	return users
}

func (*MysqlUser) SelectLimit(db *sql.DB, limit int, offset int) []model.User {
	rows, err := db.Query("select id, name, age, sex from user limit ?, ?", offset, limit);

	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	var users []model.User

	for rows.Next() {
		var user model.User
		if err := rows.Scan(&user.Id, &user.Name, &user.Age, &user.Sex); err != nil {
			log.Fatal(err)
		}
		users = append(users, user)
	}

	return users
}

func (u *MysqlUser) UpdatePassword(db *sql.DB, username string, password string) {
	hashedPassword := utils.Md5(password)
	stmt, _ := db.Prepare("UPDATE user SET password = ? WHERE name = ?")
	defer stmt.Close()
	_, err := stmt.Exec(hashedPassword, username)
	if err != nil {
		log.Println("Error %v", err)
	}
}

